package com.cpi.basilos.stock.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;

import com.cpi.basilos.corntype.dto.CornTypeDTO;
import com.cpi.basilos.stock.dto.StockDTO;
import com.cpi.basilos.stock.exception.StockException;
import com.cpi.sap.webservice.bean.GetTotalStockInputBean;
import com.cpi.sap.webservice.bean.GetTotalStockOutputBean;
import com.cpi.sap.webservice.xmlGenerator.GetTotalStockGenerator;
import com.cpi.webservice.proxy.sapinterface.SapInterfaceRemoteProxy;
import com.cs.common.exception.CSException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.xml.XMLBaseDAO;

public class StockDAO extends DBDAO {
	private static org.apache.log4j.Logger logger = Logger.getLogger(StockDAO.class);
	private static final String SQL_GET_STOCK_BY_CORN_TYPE = "select idTipoGrano, sum(cantidad) as existencia from VW_EXISTENCIA WHERE idAlmacen = ? and round(cantidad,2) <> 0 group by idTipoGrano";//$NON-NLS-1$
	private static final String SAP_PATH_WEB_SERVICE = "SAP_PATH_WEB_SERVICE";//$NON-NLS-1$
	private static final String TOTAL_STOCK_COMPLETE_NAME = "com.cpi.sap.webservice.bean.GetTotalStockOutputBean";//$NON-NLS-1$
	private static final String SQL_GET_STOCK_BY_CORN_TYPE_AND_LOCATION="select tgc.ubicacion, existencia from almtg_capac tgc LEFT OUTER JOIN existencia AS EX  ON TGC.ubicacion = EX.ubicacion AND TGC.almacen = EX.almacen AND  TGC.tipo_grano = EX.tipo_grano where TGC.almacen = ? AND TGC.tipo_grano = ? AND ( TGC.ubicacion IS NOT NULL and TGC.ubicacion != '' ) order by case when isnumeric(tgc.ubicacion)=0 then 100 else convert( int, tgc.ubicacion ) end asc, existencia desc";
	
	public ArrayList corntypesWithStock(int warehouseId)throws StockException{
		ArrayList stockDTOs = null;
		StockDTO stockDTO = null;
		CornTypeDTO cornTypeDTO = null;
		Connection connection =  null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			stockDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_STOCK_BY_CORN_TYPE, dsBasilos);
			preparedStatement.setInt(1, warehouseId);
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				stockDTO = new StockDTO();
				cornTypeDTO = new CornTypeDTO();
				cornTypeDTO.setCornTypeId(resultSet.getInt(1));
				stockDTO.setCornType(cornTypeDTO);
				stockDTO.setAmount(resultSet.getDouble(2));
				stockDTOs.add(stockDTO);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			StockException stockException = null;
			logger.error(exception);
			exception.printStackTrace(System.err);
			stockException = new StockException(exception,CSException.LAYER_DAO, CSException.ACTION_SELECT);
			throw stockException;
		}finally{
			try{
				closeConnection(connection);
			}catch(SQLException sqlException){}
		}
		return stockDTOs;
	}
	
	public double totalStockByCornTypeSAP(String cornTypeCode, int company, String warehouseCode)throws StockException{
		GetTotalStockOutputBean getTotalStockOutputBean = null;
		double totalStock = 0;
		GetTotalStockInputBean getTotalStockInputBean = null;
		java.util.Calendar date = null;
		GetTotalStockGenerator getTotalStockGenerator = null;
		String xml = null;
		String xmlResult = null;
		SapInterfaceRemoteProxy sapInterfaceRemoteProxy = null;
		XMLBaseDAO xMLBaseDAO = null;
		String sapWebService = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(SAP_PATH_WEB_SERVICE);
			sapWebService = systemParameterBean.getValue();
			date = Calendar.getInstance();
			getTotalStockInputBean = new GetTotalStockInputBean();
			xMLBaseDAO = new XMLBaseDAO();
			getTotalStockGenerator = new GetTotalStockGenerator();
			//Llenado de bean
			getTotalStockInputBean.setCodigoMaterial(cornTypeCode);
			getTotalStockInputBean.setCodigoPlanta(String.valueOf(company));
			getTotalStockInputBean.setCodigoAlmacen(warehouseCode);
			//Generaci�n de xml
			xml = getTotalStockGenerator.tanqueBeanToXML(getTotalStockInputBean);
			//Ejecuci�n del webservice
			sapInterfaceRemoteProxy = new SapInterfaceRemoteProxy(sapWebService);
			//Regreso y transformaci�n del xml
			xmlResult = sapInterfaceRemoteProxy.executeXML(xml);
			//out.println(xmlResult);
			getTotalStockOutputBean = (GetTotalStockOutputBean)xMLBaseDAO.parseTotalStockOutputBean(xmlResult, TOTAL_STOCK_COMPLETE_NAME);
			if( getTotalStockOutputBean != null ){
				getTotalStockOutputBean.setE_date(date);
				totalStock = getTotalStockOutputBean.getE_AMOUNTD();
			}else{
				totalStock = -1;
			}
		}catch(Exception exception){
			exception.printStackTrace(System.out);
			totalStock = -1;
		}
		return totalStock;
	}
	
	public List getStockList(int cornTypeId, int warehouseId) throws StockException{
		List stockDTOs = null;
		StockDTO stockDTO = null;
		Connection connection =  null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			stockDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_GET_STOCK_BY_CORN_TYPE_AND_LOCATION, dsCIMa);
			preparedStatement.setInt(1, warehouseId);
			preparedStatement.setInt(2, cornTypeId);
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				stockDTO = new StockDTO();
				stockDTO.setLocation(resultSet.getString(1));
				stockDTO.setAmount(resultSet.getDouble(2));
				stockDTOs.add(stockDTO);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			StockException stockException = null;
			logger.error(exception);
			exception.printStackTrace(System.err);
			stockException = new StockException(exception,CSException.LAYER_DAO, CSException.ACTION_SELECT);
			throw stockException;
		}finally{
			try{
				closeConnection(connection);
			}catch(SQLException sqlException){}
		}
		return stockDTOs;
	}
}